Conditional formatting

Most find it really difficult to spot the right data on their huge spreadsheets. Others face problems in quickly spotting changes in the outputs of their models as they change the inputs/assumptions. Conditional formatting is comes handy truly in such situations. From Excel 2007 onwards, conditional formatting is now in the reach of the novice. For the first time an inquisitive explorer could by himself learn and apply more than 25 predesigned effects and innumerable custom ones.

Suppose you just got hands on large customer data, multiple rows, age, income, account size etc. and before you begin the real statistical analysis, you want to have some idea of patterns. Use volume wise colour shades and you may get an idea of the relation between different variables, just have a broad minded view and observe the patterns in the colours. Having some idea of the relations and you will have headway in statistical analysis.

Having value based colours in your operations or financial models highlights the effects of inputs on your outputs. I remember once during a presentation on a variation of transportation problem, I said, “Let us close this warehouse” and pressed 0 in the inputs cell. The different dealer’s supply cells changed colours to show which warehouse was now supplying to them. Everyone says, “WOW”.

Another fun use, rather the most common use, was to apply figures based colours when the marks of any subject arrived. In four quick clicks, all toppers can be seen in greens, the laggards in reds and academic middle-class in their respective colours.

You too can make your data ranges dynamically colourful, easy to understand and your models very lovely to flaunt. Learn here.

Starter level Helper level Demigod level

 

Trace dependents and precedents

It is not easy to explain your models to others, nor is it easy to understanding others’ models. The formulae and links create cause and effect relationship between inputs and outputs. The trace precedents feature draws neat arrows and shows the various cells affecting the value of the chosen cell. Similarly trace dependents point out which all cells derive their value from a given cell.

This comes most handy while presenting your models as given the limited time, everyone would be more interested on relationships than on formulae.

Show the world the integrity of your work and ascertain theirs. Learn here:

A simple screenshot based example

 

Data tables

“DCF Valuations are result of assumptions and assumptions alone.” How many times have we heard this? In all professional reports, we see the sensitivity of the valuation to discount rates and growth rates. I have seen many of my friends creating such 6x6 table by manually entering each of 36 assumption sets and noting the results. How cumbersome!

This ‘sensitivity thing’ can be done in a snap of a finger using the Data Table feature. Moreover the table such generated is dynamic and updates itself if model’s formulae etc are changed. The sensitivity to assumptions thing can be added to any number crunching case to make the evaluator feel that you understand how they may vary from person to person.

Show the world that you value other people’s point of view by creating awesome data tables. Learn here. Just check: ‘1 way data table’ and ‘2 way data tables’ and other awesome stuff is a bonus.

A comprehensive guide

 

Pasting

No plagiarism strictly, but data is data. You have to get it from somewhere: some website, some database, some file etc. In the files of novices you can easily spot what all arrays have been ‘pasted’ by the different fonts, formatting etc.

Trust me there are more than a few ways in which you can paste in excel. Most of us would not have cared to even read the names of all the different options in the paste special dialogue box. You can choose to paste any of values, formulae, formatting, column widths, even links and many more. I remember once a friend visited me with a huge data array and a small problem which was solved just by use of Paste special and transpose, and trust me he said, “What! Ho gaya? Nahi, you are not getting....Oh it’s done!”

Do not copy, but become awesome in pasting. Learn here:

A step by step visual guide

How many times have you been successful in pasting a data array from a webpage or a PDF file? Yes there are ways to it, (other than a costly or hacked PDF to excel convertor). Copy the table from PDF, select a cell and paste (it will automatically take the required number of rows) and apply text to columns with ‘space’ as delimiter. Learn here:

Text to columns

 

Pivot tables

I sometimes do wonder if Pivot tables are overhyped. In fact it would be the second least used and most daunting major feature after Macros. You cannot do anything to any kind of data with pivot tables. Nevertheless the feature is super useful some types of analysis on some types of data.

The most useful case would be when all 3 are satisfied: (a)If you have large row wise customer data, sales data, employee data etc (b)there is at least one column in which there are numbers and (c) the sum/average etc of those numbers would make sense. Go on find the parameters for various sets, sub sets, sub-sub sets and cross sections. And in the class you can confidently say, “The XYZ should be our target segment, they blah blah blah....” Trust me, most of the data analysis cases in retail management, customer relationship management, target segment etc can be done using Pivot tables.

Remember what I said about my inability to comprehend digits? I usually add charts to my analysis using Pivot charts or applying charts to pivot tables themselves.

Create awesome analysis and wow everyone. Learn here:

The basics The star stuff

 

Hyperlinking sheets

Most of the number crunching we do is not really organized. Submitting files, with 10+ sheets, multiple levels in each sheet, of which the evaluator cannot make out the head or the tail can be a real waste of effort. You can make your files easy to understand or at least easy to navigate by adding hyperlinks. Just create an index page and put up links to all the different part of the sheets with of course proper names. The evaluator cannot avoid and will take note that you have taken care of all aspects/calculations. This is particularly helpful in a valuation model where there are multiple sheets with different data/calculations on each.

Make an index and improve navigation

 

- Anup Kumar Agarwal

(The author is an alumnus of IIM Indore, batch of 2011, and St. Xaviers College, Calcutta. He has worked with SBI Capital Markets in the past and currently runs his own venture ThinkMatrix )